package com.example.demo.util;

import lombok.AllArgsConstructor;
import org.apache.commons.io.FileExistsException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Description: 导出excel工具类
 * Excel的格式为，每一行都不能为空，获取数据以第一行为基准获取
 */
public class ExcelUtil {
    /**
     * 2003- 版本的excel
     */
    private final static String EXCEL_2003_L = ".xls";
    /**
     * 2007+ 版本的excel
     */
    private final static String EXCEL_2007_U = ".xlsx";

    private final static Logger logger = LoggerFactory.getLogger(ExcelUtil.class.getSimpleName());


    /**
     * 创建Excel操作对象
     *
     * @param is       Excel的流对象
     * @param fileName Excel的文件名
     * @return
     * @throws IOException
     */
    public static Workbook createWorkbook(InputStream is, String fileName) throws IOException {
        Workbook wb;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (EXCEL_2003_L.equals(fileType)) {
            // 2003-
            wb = new HSSFWorkbook(is);
        } else if (EXCEL_2007_U.equals(fileType)) {
            // 2007+
            wb = new XSSFWorkbook(is);
        } else {
            throw new IOException("解析的文件格式有误！");
        }
        return wb;
    }

    /**
     * 获取Excel的数据，以每个sheet的首行为表格的数据收集
     * 比如：
     *
     * @param in
     * @param fileName
     * @return
     * @throws IOException
     */
    public static List<List<List<String>>> getExcelData(InputStream in, String fileName) throws IOException {
        Workbook work = createWorkbook(in, fileName);
        int sheetLen = work.getNumberOfSheets();
        List<List<List<String>>> excelData = new ArrayList<>(sheetLen);
        for (int sheetIndex = 0; sheetIndex < sheetLen; sheetIndex++) {
            Sheet sheet = work.getSheetAt(sheetIndex);
            List<List<String>> sheetData = getSheetData(sheet);
            logger.info("该【{}】工作表已经扫描完毕。。。", work.getSheetAt(sheetIndex).getSheetName());
            excelData.add(sheetData);
        }
        return excelData;
    }

    public static List<List<String>> getSheetData(Sheet sheet) {
        Row firstRow = sheet.getRow(0);
        if (firstRow == null) {
            throw new FirstNullRowException("Excel首行不能为空");
        }
        // 获取第一行有多少列
        short maximumColumn = firstRow.getLastCellNum();
        // 最后一行的索引
        int lastRowNumIndex = sheet.getLastRowNum();
        List<List<String>> sheetData = new ArrayList<>(lastRowNumIndex + 1);
        for (int rowIndex = 0; rowIndex <= lastRowNumIndex; rowIndex++) {
            // 获取这行数据
            Row row = sheet.getRow(rowIndex);
            if (row == null) {
                // 行为空时抛出异常
                logger.error("没有数据的一行，sheet名称为{}，行号为{}", sheet.getSheetName(), rowIndex + 1);
                throw new FirstNullRowException(String.format("第%d是空行", (rowIndex + 1)));
            }
            logger.debug("获取到【{}】的第{}行。。。", sheet.getSheetName(), rowIndex + 1);
            List<String> rowData = new ArrayList<>(maximumColumn);
            for (int cellIndex = 0; cellIndex < maximumColumn; cellIndex++) {
                Cell cell = row.getCell(cellIndex);
                String cellData;
                rowData.add(cellData = parserCellValue(cell));
                logger.debug("获取到【{}】的第{}行的第{}列数据为：{}", sheet.getSheetName(), rowIndex + 1, cellIndex + 1, cell == null ? "null" : cellData);
            }
            sheetData.add(rowData);
        }
        return sheetData;
    }

    /**
     * 描述：对表格中数值进行格式化
     * <p>
     * cell.getBooleanCellValue();  getBooleanCellValue
     * cell.getDateCellValue();   getDateCellValue
     * cell.getErrorCellValue();
     * cell.getNumericCellValue();  getNumericCellValue
     * cell.getStringCellValue();
     * cell.getRichStringCellValue();  getRichStringCellValue
     *
     * @param cell
     * @return
     */
    private static String parserCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        String value;
        CellType cellType = cell.getCellType();
        if (CellType.STRING.equals(cellType)) {
            value = cell.getRichStringCellValue().getString();
        } else if (CellType.NUMERIC.equals(cellType)) {
            // 整数，分数，日期
            String formatType = cell.getCellStyle().getDataFormatString();
            if ("General".equals(formatType)) {
                //格式化数字  #.## 标识保留两位小数，如果没有小数就不要，比如  2.1==》2.1
                //格式化数字  0.00 标识保留两位小数，如果没有小数就填零，比如  2.1==》2.10
                value = new DecimalFormat("#.####").format(cell.getNumericCellValue());
            } else {
                //日期格式化
                value = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cell.getDateCellValue());
            }
        } else if (CellType.BOOLEAN.equals(cellType)) {
            value = cell.getBooleanCellValue() + "";
        } else {
            throw new UnSupportCellTypeException("不支持的表格类型处理");
        }
        return value;
    }


    //==================================================================================================================


    /**
     * 导出 Excel 文件
     *
     * @param titles
     * @param dataList
     * @param xlsPath
     * @throws IOException
     */
    public static void createXls(String[] titles, List<Object[]> dataList, String xlsPath) throws IOException {
        File file = new File(xlsPath);
        if (file.exists()) {
            throw new FileExistsException("该路径已存在文件");
        }
        File parentFile = file.getParentFile();
        if (!parentFile.exists()) {
            parentFile.mkdirs();
        }
        file.createNewFile();
        FileOutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(file);
            createXls(titles, dataList, "Sheet0", outputStream);
        } finally {
            FileUtil.close(outputStream);
        }
    }


    /**
     * 导出 Excel 文件
     *
     * @param titles       第一行的标题
     * @param dataList     其它行的所有数据
     * @param sheetName    表名
     * @param outputStream 输出流
     */
    public static void createXls(String[] titles, List<Object[]> dataList, String sheetName, OutputStream outputStream) throws IOException {
        if (titles == null) {
            throw new IllegalArgumentException("titles不能为空");
        }
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 创建第一行
        HSSFRow firstRow = sheet.createRow(0);
        for (int columnIndex = 0; columnIndex < titles.length; columnIndex++) {
            HSSFCell cell = firstRow.createCell(columnIndex, CellType.STRING);
            cell.setCellValue(titles[columnIndex]);
        }
        if (dataList != null) {
            // 追加其它数据
            for (int rowNum = 1; rowNum < dataList.size(); rowNum++) {
                // 创建列
                HSSFRow row = sheet.createRow(rowNum);
                Object[] data = dataList.get(rowNum - 1);
                for (int i = 0; i < data.length; i++) {
                    Object cellDate = data[i];
                    CellType cellType = getCellType(cellDate);
                    HSSFCell cell = row.createCell(i, cellType);
                    setCellValue(cell, cellDate);
                }
            }
        }
        workbook.write(outputStream);
    }

    private static void setCellValue(HSSFCell cell, Object cellDate) {
        if (cellDate == null) {
            cell.setCellValue("");
        }
        if (cellDate instanceof String) {
            cell.setCellValue((String) cellDate);
        } else if (cellDate instanceof Number) {
            cell.setCellValue(Double.parseDouble(cellDate + ""));
        } else {
            cell.setCellValue(cellDate + "");
        }
    }

    private static CellType getCellType(Object cellDate) {
        if (cellDate == null || cellDate instanceof String) {
            return CellType.STRING;
        } else if (cellDate instanceof Number) {
            return CellType.NUMERIC;
        } else {
            return CellType.BLANK;
        }
    }

    public static class UnSupportCellTypeException extends RuntimeException {
        public UnSupportCellTypeException(String message) {
            super(message);
        }
    }

    /**
     * 当Excel解析时发现某一行行没有数据时抛出异常
     */
    public static class NullRowException extends RuntimeException {

        public NullRowException(String message) {
            super(message);
        }

    }

    /**
     * 当Excel解析时发现第一行没有数据时抛出异常
     */
    public static class FirstNullRowException extends NullRowException {

        public FirstNullRowException(String message) {
            super(message);
        }

    }
}